Download
FAQ
History
HomeHomeNext API
Search
Feedback
Divider

Accessing Databases from Web Applications

Data that is shared between Web components and persistent between invocations of a Web application is usually maintained in a database. Web applications use the JDBC 2.0 API to access relational databases. For information on this API, see

http://java.sun.com/docs/books/tutorial/jdbc 

In the JDBC API, databases are accessed via DataSource objects. A DataSource has a set of properties that identify and describe the real world data source that it represents. These properties include information like the location of the database server, the name of the database, the network protocol to use to communicate with the server, and so on.

Applications access a data source using a connection, and a DataSource object can be thought of as a factory for connections to the particular data source that the DataSource instance represents. In a basic DataSource implementation, a call to the method DataSource.getConnection returns a connection object that is a physical connection to the data source.

If a DataSource object is registered with a JNDI naming service, an application can use the JNDI API to access that DataSource object, which can then be used to connect to the data source it represents.

DataSource objects that implement connection pooling also produce a connection to the particular data source that the DataSource class represents. The connection object that the method DataSource.getConnection returns is a handle to a PooledConnection object rather than being a physical connection. An application uses the connection object like any other connection. Connection pooling has no effect on application code except that a pooled connection, like all connections, should always be explicitly closed. When an application closes a connection that is pooled, the connection is returned to a pool of reusable connections. The next time DataSource.getConnection is called, a handle to one of these pooled connections will be returned if one is available. Because connection pooling avoids creating a new physical connection every time one is requested, it can help applications run significantly faster.

The Duke's Bookstore examples use the PointBase evaluation database included with the J2EE 1.4 Application Server to maintain the catalog of books. This section describes how to:

Starting the PointBase Database Server

To start the PointBase database server:

  1. In a terminal window, go to <J2EE_HOME>/pointbase/tools/serveroption.
  2. Execute the startserver script.

On Windows, from the Start menu, choose

ProgramsRight ArrowSun MicrosystemsRight ArrowJ2EE 1.4 SDKRight ArrowStart PointBase

Populating the Example Database

To populate the database for the Duke's Bookstore examples:

  1. In a terminal window, go to <INSTALL>/j2eetutorial14/examples/web/bookstore.
  2. Run asant create-db_common. This asant task runs a PointBase commander tool command to read the file books.sql and execute the SQL commands contained in the file. The table named books is created for the user pbpublic in the sun-appserv-samples PointBase database.
  3. At the end of the processing, you should see the following output:
  4. ...
    [java] SQL> INSERT INTO books VALUES('207', 'Thrilled', 'Ben',
    [java]  'The Green Project: Programming for Consumer Devices',
    [java]  30.00, false, 1998, 'What a cool book', 20);
    [java] 1 row(s) affected
    
    [java] SQL> INSERT INTO books VALUES('208', 'Tru', 'Itzal',
    [java]  'Duke: A Biography of the Java Evangelist',
    [java]  45.00, true, 2001, 'What a cool book.', 20);
    [java] 1 row(s) affected 
    

You can check that the table exists with the PointBase console tool as follows:

  1. In a terminal window, go to <J2EE_HOME>/pointbase/tools/serveroption/.
  2. Execute startconsole.
  3. In the Connect to Database dialog enter jdbc:pointbase:server://localhost/sun-appserv-samples in the URL field.
  4. Click OK.
  5. Expand the SCHEMASRight ArrowPBPUBLICRight ArrowTABLES nodes. Notice that there is a table named BOOKS.
  6. To see the contents of the books table:
    1. In the Enter SQL commands text area, enter select * from books;.
    2. Click the Execute button.

Defining a Data Source in the J2EE Server

Data sources in the J2EE application server implement connection pooling. To define the Duke's Bookstore data source, you first need to create a data pool as follows:

  1. In a terminal window, go to <INSTALL>/j2eetutorial14/examples/web/bookstore.
  2. Run asant create-jdbc-connection-pool_common. This asant task runs an asadmin command to create a JDBC connection pool named bookstore-pool.
  3. At the end of the processing, you should see the following output:
  4. admin_command_common:
         [echo] Doing admin task set server.jdbc-connection-
    pool.bookstore-pool.property.Password=pbpublic
    [sun-appserv-admin] Executing: set --port 4848 --host localhost 
    --password yourpassword --user admin server.jdbc-connection-
    pool.bookstore-pool.property.Password=pbpublic
    [sun-appserv-admin] Unable to read system environment. No 
    system environment will be used.
    [sun-appserv-admin] Attribute property.Password set to pbpublic 
    

Then, create the data source as follows:

  1. In a terminal window, go to <INSTALL>/j2eetutorial14/examples/web/bookstore.
  2. Run asant create-jdbc-resource_common. This asant task runs an asadmin command to create a JDBC resource named jdbc/BookDB that references the bookstore-pool connection pool.
  3. At the end of the processing, you should see the following output:
  4. admin_command_common:
         [echo] Doing admin task create-jdbc-resource           --
    connectionpoolid bookstore-pool --instance server jdbc/BookDB
    [sun-appserv-admin] Executing: create-jdbc-resource 
    --port 4848 --host localhost --password yourpassword 
    --user admin --connectionpoolid bookstore-pool 
    --instance server jdbc/BookDB
    [sun-appserv-admin] Created the external JDBC resource with 
    jndiname = jdbc/BookDB 
    

When you change application server configuration information, the changes are not applied immediately, but are saved into special files, located in <J2EE_HOME>/domains/domain1/config/backup. Until you apply the changes, they do not take effect. Applying changes is also called reconfiguring the server. When you apply your changes, all changes made to the configuration since the last time you applied changes take effect. Note that restarting the server does not automatically apply the changes.

To reconfigure the server:

  1. Run asant reconfig_common. This asant task runs an asadmin command to reconfigure the server.
  2. At the end of the processing, you should see the following output:
  3. reconfig_common:
    [echo] Reconfiguring server server
    [sun-appserv-admin] Executing: reconfig --port 4848 
    --host localhost --password yourpassword --user admin server
    [sun-appserv-admin] Successfully reconfigured 
    

Configuring the Web Application to Reference a Data Source with JNDI

In order to access a database from a Web application, you must declare resource reference in the application's Web application deployment descriptor (see Declaring References to Environment Entries, Resource Environment Entries, or Resources). The resource reference declares a JNDI name, the type of the data resource, and the kind of authentication used when the resource is accessed. The JNDI name is used to create a data source object in the database helper class database.BookDB:

public BookDB () throws Exception {
  try  { 
    Context initCtx = new InitialContext();
    Context envCtx = (Context)
      initCtx.lookup("java:comp/env");
    DataSource ds = (DataSource) envCtx.lookup("jdbc/BookDB");
    con =  ds.getConnection();
    System.out.println("Created connection to database.");
  } catch (Exception ex) {
    System.out.println("Couldn't create connection." + 
    ex.getMessage());
    throw new 
      Exception("Couldn't open connection to database: "
    +ex.getMessage());
  }  

To specify a resource reference to the bookstore data source:

  1. Select the WAR.
  2. Select the References tab.
  3. Click Add.
  4. Type jdbc/BookDB in the Name field.
  5. Accept the default type javax.sql.DataSource.
  6. Accept the default authorization Container.
  7. Accept the default Shareable selected.

Mapping the Web Application JNDI Name to a Data Source

Since the resource reference declared in the Web application deployment descriptor uses a JNDI name to refer to the data source, you must connect the name to a data source defined by the J2EE application server as follows:

  1. Select the Resource Reference Name, jdbc/BookDB, defined in the previous section.
  2. In the Deployment Setting box, enter the name of the data source that you create in the application server, jdbc/BookDB, in the JNDI Name field.
Divider
Download
FAQ
History
HomeHomeNext API
Search
Feedback
Divider

All of the material in The J2EE 1.4 Tutorial is copyright-protected and may not be published in other works without express written permission from Sun Microsystems.